package com.bolt.support.codegenerator.db;


import com.bolt.common.convert.Convert;
import com.bolt.common.db.DbHelper;
import com.bolt.common.db.converts.*;
import com.bolt.common.utils.StrUtil;
import com.bolt.convention.exception.ServiceException;
import com.bolt.support.codegenerator.model.Column;
import com.bolt.support.codegenerator.model.DbType;
import com.bolt.support.codegenerator.model.Table;
import com.bolt.support.codegenerator.util.JdbcTypeResolver;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

/**
 * Created by Administrator on 2018/2/2.
 */
@Slf4j
public abstract class AbstractTableFactory implements TableFactory {

    private static final String COLUMN_NAME = "COLUMN_NAME";

    protected Connection connection;

    protected String url;
    protected String username;
    protected String password;
    protected String driverClass;

    protected DbType dbType;

    protected IJdbcTypeConvert typeConvert;


    public AbstractTableFactory(String url, String username, String password, String driverClass) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.driverClass = driverClass;
    }

    public Connection getConnection() {
        try {
            if (connection == null || connection.isClosed()) {
                Properties props = new Properties();
                props.setProperty("user", username);
                props.setProperty("password", password);
                props.put("remarksReporting", "true");
                connection = DbHelper.getConnection(url, driverClass, props);
                return connection;
            } else {
                return connection;
            }
        } catch (SQLException ex) {
            throw new ServiceException(ex);
        }
    }


    /**
     * 判断数据库类型
     *
     * @return 类型枚举值
     */
    public DbType getDbType(String driverClass) {
        if (null == dbType) {
            if (driverClass.contains("mysql")) {
                dbType = DbType.MYSQL;
            } else if (driverClass.contains("oracle")) {
                dbType = DbType.ORACLE;
            } else if (driverClass.contains("postgresql")) {
                dbType = DbType.POSTGRE_SQL;
            } else if (driverClass.contains("db2")) {
                dbType = DbType.DB2;
            } else if (driverClass.contains("mariadb")) {
                dbType = DbType.MARIADB;
            } else {
                throw new ServiceException("Unknown type of database!");
            }
        }
        return dbType;
    }

    public IJdbcTypeConvert getTypeConvert() {
        if (null == typeConvert) {
            switch (getDbType(this.driverClass)) {
                case ORACLE:
                    typeConvert = new OracleJdbcTypeConvert();
                    break;
                case SQL_SERVER:
                    typeConvert = new SqlServerJdbcTypeConvert();
                    break;
                case POSTGRE_SQL:
                    typeConvert = new PostgreSqlJdbcTypeConvert();
                    break;
                case DB2:
                    typeConvert = new DB2JdbcTypeConvert();
                    break;
                case MARIADB:
                    typeConvert = new MySqlJdbcTypeConvert();
                    break;
                default:
                    // 默认 MYSQL
                    typeConvert = new MySqlJdbcTypeConvert();
                    break;
            }
        }
        return typeConvert;
    }


    public synchronized static TableFactory getInstance(String url, String username, String password, String driverClass) {
        String dbProduct = StrUtil.subBetween(url, "jdbc:", ":");
        if (dbProduct.toLowerCase().contains("oracle")) {
            return new OracleTableFactory(url, username, password, driverClass);
        } else if (dbProduct.toLowerCase().contains("mysql")) {
            return new MysqlTableFactory(url, username, password, driverClass);
        } else if (dbProduct.toLowerCase().contains("postgresql")) {
            return new PostgreSqlTableFactory(url, username, password, driverClass);
        } else {
            throw new ServiceException("不支持[" + dbProduct + "]数据库类型");
        }
    }


    /**
     * 查询表注释
     *
     * @param table
     * @param commentSql     查询表注释的SQL, 含一个占位符 (tableName)
     * @param columnComments 注释列列名
     * @throws SQLException
     */
    protected void introspectTableComments(Table table, String commentSql, String columnComments) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            psmt = getConnection().prepareStatement(commentSql);
            psmt.setString(1, table.getSqlName());
            rs = psmt.executeQuery();
            if (rs.next()) {
                table.setRemarks(rs.getString(columnComments));
            }
        } catch (SQLException e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(rs, psmt);
        }
    }

    /**
     * 查询表中列注释
     *
     * @param table
     * @param colCommentSql    查询表中列注释的SQL, 含一个占位符 (tableName)
     * @param columnColumnName 字段名列列名
     * @param columnComments   注释列列名
     * @throws SQLException
     */
    protected void introspectTableColumnsComments(Table table, String colCommentSql, String columnColumnName,
                                                  String columnComments) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            psmt = getConnection().prepareStatement(colCommentSql);
            psmt.setString(1, table.getSqlName());
            rs = psmt.executeQuery();
            while (rs.next()) {
                String columnName = rs.getString(columnColumnName);
                Column column = table.getColumn(columnName);
                if (column != null) {
                    column.setRemarks(rs.getString(columnComments));
                }
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            DbHelper.close(rs, psmt);
        }
    }

    /**
     * 查询表结构
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    @Override
    public Table getTable(String tableName) throws SQLException {
        ResultSet rs = null;
        Table table = null;
        try {
            DatabaseMetaData dbMetaData = getConnection().getMetaData();
            String dbProduct = dbMetaData.getDatabaseProductName();
            String schema = null;
            if (dbProduct.toLowerCase().contains("oracle")) {
                schema = dbMetaData.getUserName();
            }
            rs = dbMetaData.getTables(null, schema, tableName, new String[]{"TABLE", "VIEW"});
            if (rs.next()) {
                table = new Table();
                table.setSqlName(tableName);
                table.setCatalog(rs.getString("TABLE_CAT"));
                table.setSchema(rs.getString("TABLE_SCHEM"));
                table.setRemarks(rs.getString("REMARKS"));
                table.setTableType(rs.getString("TABLE_TYPE"));
                introspectColumns(table);
                introspectPrimaryKeys(table);

            }
        } catch (SQLException e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(rs);
        }
        return table;
    }

    /**
     * 查询所有表(只取出表名与注解)
     *
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, String> getAllTables() throws SQLException {

        DatabaseMetaData dbMetaData = getConnection().getMetaData();
        String dbProduct = dbMetaData.getDatabaseProductName();
        String schema = null;
        if (dbProduct.toLowerCase().contains("oracle")) {
            schema = dbMetaData.getUserName();
        }
        String[] types = {"TABLE", "VIEW"};
        ResultSet rs = dbMetaData.getTables(null, schema, "%", types);
        Map<String, String> tables = new LinkedHashMap<String, String>();
        while (rs.next()) {
            tables.put(rs.getString("TABLE_NAME"), rs.getString("REMARKS"));
        }
        return tables;
    }

    /**
     * 处理主键字段
     *
     * @param table
     * @throws SQLException
     */
    protected void introspectPrimaryKeys(Table table) throws SQLException {
        ResultSet rs = null;
        try {
            rs = getConnection().getMetaData().getPrimaryKeys(table.getCatalog(), table.getSchema(), table.getSqlName());
            while (rs.next()) {
                String columnName = rs.getString(COLUMN_NAME);
                Column column = table.getColumn(columnName);
                column.setPrimaryKey(true);
                table.setPkColumn(column);
            }
        } catch (SQLException e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(rs);
        }
    }

    /**
     * 处理表列
     *
     * @param table
     * @throws ServiceException
     */
    protected void introspectColumns(Table table) throws ServiceException {
        ResultSet rs = null;
        try {
            DatabaseMetaData dbMetaData = getConnection().getMetaData();
            rs = dbMetaData.getColumns(null, table.getSchema(), table.getSqlName(), "%");
            while (rs.next()) {
                String columnName = rs.getString(COLUMN_NAME);
                if (StrUtil.isEmpty(columnName)) {
                    continue;
                }

                Column column = table.getColumn(columnName);
                if (column == null) {
                    column = new Column(columnName);
                    table.addColumn(column);
                }
                column.setJdbcType(rs.getInt("DATA_TYPE"));
                column.setSize(rs.getInt("COLUMN_SIZE"));
                column.setNullable(rs.getInt("NULLABLE") == 1);
                column.setDefaultValue(rs.getString("COLUMN_DEF"));
                column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
                column.setRemarks(rs.getString("REMARKS"));
                if (hasColumn(rs, "IS_AUTOINCREMENT")) {
                    column.setAutoincrement(Convert.toBool(rs.getString("IS_AUTOINCREMENT"), false));

                }
                column.setJdbcTypeName(JdbcTypeResolver.getJdbcTypeName(column.getJdbcType()));
                JavaType javaType = getTypeConvert().processTypeConvert(column.getJdbcTypeName());
                column.setJavaType(javaType.getType());
                column.setFullJavaType(javaType.getPkg());
                column.setJavaProperty(column.getSqlName());
            }

        } catch (SQLException e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(rs);
        }
    }


    /**
     * @param rs
     * @param columnName
     * @return
     * @throws SQLException
     */
    protected boolean hasColumn(ResultSet rs, String columnName) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columns = rsmd.getColumnCount();
        for (int x = 1; x <= columns; x++) {
            if (columnName.equals(rsmd.getColumnName(x))) {
                return true;
            }
        }
        return false;
    }




}
